﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace YQGL.DBUtility
{
    public class PagerQuery
    {
        private int _pageIndex;
        private int _pageSize = 20;
        private string _pk;
        private string _fromClause;
        private string _groupClause;
        private string _selectClause;
        private string _sortClause;
        private StringBuilder _whereClause;
        public DateTime DateFilter = DateTime.MinValue;

        public PagerQuery()
        {
            _whereClause = new StringBuilder();
        }

        /// <summary>
        /// 主键
        /// </summary>
        public string PK
        {
            get { return _pk; }
            set { _pk = value; }
        }

        public string SelectClause
        {
            get { return _selectClause; }
            set { _selectClause = value; }
        }

        public string FromClause
        {
            get { return _fromClause; }
            set { _fromClause = value; }
        }

        public StringBuilder WhereClause
        {
            get { return _whereClause; }
            set { _whereClause = value; }
        }

        public string GroupClause
        {
            get { return _groupClause; }
            set { _groupClause = value; }
        }

        public string SortClause
        {
            get { return _sortClause; }
            set { _sortClause = value; }
        }


        /// <summary>
        /// 当前页数
        /// </summary>
        public int PageIndex
        {
            get { return _pageIndex; }
            set { _pageIndex = value; }
        }


        /// <summary>
        /// 分页大小
        /// </summary>
        public int PageSize
        {
            get { return _pageSize; }
            set { _pageSize = value; }
        }


        /// <summary>
        /// 生成分页查询语句
        /// </summary>
        /// <returns></returns>
        public string GenerateSql()
        {
            StringBuilder sb = new StringBuilder();
            if (string.IsNullOrEmpty(SelectClause))
                SelectClause = "*";

            if (string.IsNullOrEmpty(SortClause))
                SortClause = PK;

            int start_row_num = (PageIndex - 1) * PageSize + 1;

            sb.AppendFormat(" from {0}", FromClause);
            if (WhereClause.Length > 0)
                sb.AppendFormat(" where 1=1 {0}", WhereClause);

            if (!string.IsNullOrEmpty(GroupClause))
                sb.AppendFormat(" group by {0}", GroupClause);

            return
              string.Format(
                "WITH t AS (SELECT ROW_NUMBER() OVER(ORDER BY {0}) as row_number,{1}{2}) Select * from t where row_number BETWEEN {3} and {4}",
                SortClause, SelectClause, sb, start_row_num, (start_row_num + PageSize - 1));
        }




        public string GenerateSqlAll()
        {
            StringBuilder sb = new StringBuilder();
            if (string.IsNullOrEmpty(SelectClause))
                SelectClause = "*";

            if (string.IsNullOrEmpty(SortClause))
                SortClause = PK;

            int start_row_num = (PageIndex - 1) * PageSize + 1;

            sb.AppendFormat(" from {0}", FromClause);
            if (WhereClause.Length > 0)
                sb.AppendFormat(" where 1=1 {0}", WhereClause);

            if (!string.IsNullOrEmpty(GroupClause))
                sb.AppendFormat(" group by {0}", GroupClause);

            return
              string.Format(
                "SELECT {1}{2} ORDER BY {0}",
                SortClause, SelectClause, sb, start_row_num, (start_row_num + PageSize - 1));
        }
        /// <summary>
        /// 生成查询记录总数的SQL语句
        /// </summary>
        /// <returns></returns>
        public string GenerateCountSql()
        {
            StringBuilder sb = new StringBuilder();

            sb.AppendFormat(" from {0}", FromClause);
            if (WhereClause.Length > 0)
                sb.AppendFormat(" where 1=1 {0}", WhereClause);

            if (!string.IsNullOrEmpty(GroupClause))
                sb.AppendFormat(" group by {0}", GroupClause);

            return string.Format("Select count(0) {0}", sb);
        }
    }
}
